package com.ben.multiple.poi;

import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Poi工具类
 *
 * @author ben
 * @date 2019/5/29
 */
@Slf4j
public class PoiUtils {

    /**
     * 获取 sheet 中的数据
     *
     * @param sheet          Sheet
     * @param startIndex     int 起始行
     * @param headerProperty String[]    每列对应的 实体类属性名
     * @return List<Map < String, String>>
     */
    public static List<Map<String, String>> getDataFromSheet(Sheet sheet, int startIndex, String[] headerProperty) {
        if (sheet == null || sheet.getLastRowNum() < startIndex || headerProperty == null || headerProperty.length == 0) {
            throw new RuntimeException("表格无数据或参数异常");
        }
        List<Map<String, String>> data = new ArrayList<>();
        for (int i = startIndex; i <= sheet.getLastRowNum(); i++) {
            Map<String, String> map = new HashMap<>();
            Row row = sheet.getRow(i);
            for (int j = 0; j < headerProperty.length; j++) {
                Cell cell = row.getCell(j);
                String cellValue = getCellString(cell);
                if ("".equals(cellValue)) {
                    throw new RuntimeException("第 " + row.getRowNum() + " 行数据格式错误");
                }
                map.put(headerProperty[j], cellValue);
            }
            data.add(map);
        }
        return data;
    }

    /**
     * 生成 Workbook
     *
     * @param data   List<List<String>>
     * @param header String[]
     * @return Workbook
     */
    public static Workbook generateWorkbook(List<List<String>> data, String[] header) {
        if (data == null || data.size() == 0 || header == null || header.length == 0) {
            throw new RuntimeException("参数错误");
        }
        Workbook workbook;
        try {
            workbook = WorkbookFactory.create(true);
        } catch (IOException e) {
            log.error("创建工作簿失败", e);
            throw new RuntimeException("创建工作簿失败");
        }
        Sheet sheet = workbook.createSheet();
        sheet.setDefaultColumnWidth(20);
        // 首行
        Row row = sheet.createRow(0);
        CellStyle menuCellStyle = getMenuCellStyle(workbook);
        for (int i = 0; i < header.length; i++) {
            Cell cell = row.createCell(i);
            cell.setCellValue(header[i]);
            cell.setCellStyle(menuCellStyle);
        }
        // 数据
        CellStyle generalCellStyle = getGeneralCellStyle(workbook);
        for (int i = 1; i <= data.size(); i++) {
            Row dataRow = sheet.createRow(i);
            List<String> list = data.get(i - 1);
            if (list == null || list.size() < header.length) {
                throw new RuntimeException("参数错误");
            }
            for (int j = 0; j < header.length; j++) {
                Cell cell = dataRow.createCell(j);
                cell.setCellValue(list.get(j));
                cell.setCellStyle(generalCellStyle);
            }
        }
        return workbook;
    }

    /**
     * 输出文件
     *
     * @param response HttpServletResponse
     * @param workbook Workbook
     */
    public static void responseWorkbook(HttpServletResponse response, Workbook workbook) {
        try {
            ServletOutputStream outputStream = response.getOutputStream();
            response.setCharacterEncoding(StandardCharsets.UTF_8.name());
            response.setContentType("application/vnd.ms-excel;charset=UTF-8");
            String fileName = new String((System.currentTimeMillis() + ".xls").getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            response.addHeader("Access-Control-Allow-Headers", "Content-Type,Content-Disposition");
            response.addHeader("Access-Control-Expose-Headers", "Content-Type,Content-Disposition");
            workbook.write(outputStream);
            workbook.close();
            outputStream.close();
        } catch (IOException e) {
            log.error("导出文件失败", e);
            throw new RuntimeException("导出文件失败");
        }
    }

    /**
     * 获取单元格中的数据，已字符串格式返回，去除空白字符
     *
     * @param cell Cell
     * @return String
     */
    private static String getCellString(Cell cell) {
        if (cell == null) {
            return "";
        }
        String cellStringValue;
        switch (cell.getCellType()) {
            case STRING:
                cellStringValue = cell.getStringCellValue();
                break;
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    cellStringValue = cell.getDateCellValue().toString();
                } else {
                    cellStringValue = BigDecimal.valueOf(cell.getNumericCellValue()).toPlainString();
                }
                break;
            case BOOLEAN:
                cellStringValue = cell.getBooleanCellValue() + "";
                break;
            case FORMULA:
                cellStringValue = cell.getCellFormula();
                break;
            default:
                cellStringValue = "";
                break;
        }
        return cellStringValue.trim().replaceAll("(\\s)?(\\r)?(\\n)?", "");
    }

    /**
     * 创建标题样式
     *
     * @param workbook 表
     */
    private static CellStyle getTitleCellStyle(Workbook workbook) {

        CellStyle style = workbook.createCellStyle();
        //水平居中
        style.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //不自动换行
        style.setWrapText(false);
        //设置背景颜色    无效
        style.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        style.setFont(getTitleFontStyle(workbook));
        return style;
    }

    /**
     * 创建标题字体
     *
     * @param workbook 工作簿
     */
    private static Font getTitleFontStyle(Workbook workbook) {
        Font font = workbook.createFont();
        //加粗
        font.setBold(true);
        //字体高度
        font.setFontHeightInPoints((short) 32);
        //字体类型
        font.setFontName(FontType.BLACK_BODY.getRemark());
        //设置字体颜色
        font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
        return font;
    }

    /**
     * 创建菜单栏样式
     *
     * @param workbook 表
     */
    private static CellStyle getMenuCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(false);
        style.setFillForegroundColor(IndexedColors.GREEN.index);
        style.setFont(getMenuFontStyle(workbook));
        return style;
    }

    /**
     * 创建菜单栏字体
     *
     * @param workbook 工作簿
     */
    private static Font getMenuFontStyle(Workbook workbook) {
        Font font = workbook.createFont();
        //加粗
        font.setBold(true);
        //字体高度
        font.setFontHeightInPoints((short) 24);
        //字体类型
        font.setFontName(FontType.REGULAR_SCRIPT.getRemark());
        font.setColor(HSSFColor.HSSFColorPredefined.BLUE.getIndex());
        return font;
    }

    /**
     * 创建普通单元格样式
     *
     * @param workbook 表
     */
    private static CellStyle getGeneralCellStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setWrapText(false);
        style.setFillForegroundColor(IndexedColors.PINK.index);
        style.setFont(getGeneralFontStyle(workbook));
        return style;
    }

    /**
     * 创建通用字体
     *
     * @param workbook 工作簿
     */
    private static Font getGeneralFontStyle(Workbook workbook) {
        Font font = workbook.createFont();
        //加粗
        font.setBold(false);
        //字体高度
        font.setFontHeightInPoints((short) 20);
        //字体类型
        font.setFontName(FontType.SONG_STYLE.getRemark());
        return font;
    }

    /**
     * 根据不同浏览器，采用不同的编码方式
     *
     * @param request HttpServletRequest
     * @param name    String 文件名
     * @return String
     */
    private static String getFileName(HttpServletRequest request, String name) {
        String header = request.getHeader("User-Agent");
        if (header != null) {
            if (header.contains("Edge") || header.contains("Trident") || header.contains("MSIE")) {
                try {
                    return URLEncoder.encode(name, "UTF-8").replaceAll("\\+", "%20");
                } catch (UnsupportedEncodingException e) {
                    return getGeneralFileName(name);
                }
            }
            if (header.contains("Firefox") || header.contains("Chrome")) {
                return new String(name.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
            }
            return getGeneralFileName(name);
        }
        return getGeneralFileName(name);
    }

    /**
     * 获取通用的文件名
     *
     * @param filename String  文件名
     * @return String
     */
    private static String getGeneralFileName(String filename) {
        try {
            String s = URLEncoder.encode(filename, "UTF-8").replaceAll("\\+", "%20");
            return "\"" + s + "\";" + "filename*=utf-8''" + s;
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        }
        return "";
    }

    /**
     * 字体枚举
     */
    enum FontType {
        /**
         * 字体
         */
        BLACK_BODY("黑体"),
        REGULAR_SCRIPT("楷体"),
        SONG_STYLE("宋体");
        private String remark;

        FontType(String remark) {
            this.remark = remark;
        }

        public String getRemark() {
            return remark;
        }
    }
}
